package rad.Java.Common.DB;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import rad.Java.Common.Util.Functions;
import rad.Java.Common.Util.Constants;

public class GenerateSql
{
    /*atributes*/
    public enum CommandType { CMD_INSERT, CMD_UPDATE, CMD_DELETE, CMD_SELECT; };

    private String              _table;
    private CommandType         _commandType;
    private ArrayList<String>   _fieldName;
    private ArrayList<String>   _fieldValue;
    private ArrayList<String>   _conditionalWhere;
    private ArrayList<String>   _junction;
    private String              _mensagemErro;
    private String              _sql;
    private ArrayList<String>   _additional;
    private ResultSet           _resultSet;

    /*constructor*/
    public GenerateSql()
    {
        _fieldName = new ArrayList<String>();
        _fieldValue = new ArrayList<String>();
        _conditionalWhere = new ArrayList<String>();
        _additional= new ArrayList<String>();
        _junction = new ArrayList<String>();
    }

    /*properties*/
    public void setTable(String value)
    {
        _table = value;
    }


    public String getTable() 
    { 
        return _table;
    }
    
    
    public String getMsgErro()
    {
        return _mensagemErro;
    }


    public void setConditionalWhere(String value)
    {
        _conditionalWhere.add(value);
    }

    public void setAditionalWhere(String value)
    {
        _additional.add(value);
    }
    
    public String  getSql() 
    { 
        return _sql;
    }
    
    
    public void    setSql(String value) 
    { 
        _sql = value;
    }

    
    public CommandType  getCommandType() 
    { 
        return _commandType;
    }
    
    
    public void setCommandType(CommandType value) 
    { 
        _commandType = value;
    }

    
    /*methods*/
    public Boolean execute(Boolean autoCommit)
    {
        if(_commandType == CommandType.CMD_INSERT)
                _sql = getSqlInsert();
        else if( _commandType == CommandType.CMD_DELETE)
                _sql = getSqlDelete();
        else if( _commandType == CommandType.CMD_UPDATE)
                _sql = getSqlUpDate();
        else if( _commandType == CommandType.CMD_SELECT)
            _sql = getSqlSelect();

        if( _sql == null)
            return false;

        if( _commandType == CommandType.CMD_SELECT)
            _resultSet = Database.executeQueryRs(_sql);
        else
            Database.executeCommand(_sql, autoCommit);

        if (Database.Error)
        {
            //LogErro.writeError(fDatabase.ErrorMessage);
            //LogErro.writeErrorSql(fSql);
            _mensagemErro = Database.ErrorMessage;
            return false;
        }
        return true;
    }


    public ResultSet getResultSet() throws SQLException
    {
        return _resultSet;
    }

    public boolean next() throws SQLException
    {
        return _resultSet.next();
    }

    public String getSqlDelete()
    {
        String sWhere;

        /* get where*/
        if (_conditionalWhere.size() != 0)
        {
            sWhere = " WHERE " + _conditionalWhere.get(0);
            for (int i = 1; i < _conditionalWhere.size(); i++)
                sWhere += " AND " + _conditionalWhere.get(i);
        }
        else sWhere = " ";

        return "DELETE FROM " + _table + sWhere;
    }


    public String getSqlInsert()
    {
        String sSQLFields="";
        String sSQLValues="";
        int cont=0;
        
        try
        {            
            for (int i = 0; i < _fieldName.size(); i++)
            {
                if (!_fieldValue.get(i).equals(""))
                {
                    if (i==0)
                    {
                        sSQLFields = _fieldName.get(0);
                        sSQLValues = "'" + _fieldValue.get(0) + "'";
                    }
                    else
                    {
                            sSQLFields += ", " + _fieldName.get(i);
                            sSQLValues += ", '" + _fieldValue.get(i) + "'";
                    }
                    cont++;
                }
            }

            if( cont == 0)
                return null;            
        }
        catch(Exception e)
        {
            sSQLFields = "";
            sSQLValues = "";
            //LogErro.writeError("------------------------------------------------------------------------------------");
            //LogErro.writeError("Exceção da peste: "+e.toString());
            //LogErro.writeError("------------------------------------------------------------------------------------");
        }
        return "INSERT INTO " + _table + " (" + sSQLFields + ") VALUES (" + sSQLValues + ")";
    }


    public String getSqlUpDate()
    {
        String sSQL;
        String sWhere;

        int cont = 0;
        /* get names and values*/
        sSQL = "";
        for (int i = 0; i < _fieldName.size(); i++)
        {
            if (!_fieldValue.get(i).equals(""))
            {
                if (sSQL.equals(""))
                    sSQL = _fieldName.get(i) + "= '" + _fieldValue.get(i) + "'";
                else
                    sSQL = sSQL + ", " + _fieldName.get(i) + "= '" + _fieldValue.get(i) + "'";
                cont++;
            }
        }

        if( cont == 0)
            return null;
        
        /* get where*/
        if (_conditionalWhere.size() != 0)
        {
            sWhere = " WHERE " + _conditionalWhere.get(0);
            for (int i = 1; i < _conditionalWhere.size(); i++)
                sWhere += " AND "+_conditionalWhere.get(i);
        }
        else
            sWhere = "";

        return "UPDATE " + _table + " SET " + sSQL + sWhere;
    }

    public String getSqlSelect()
    {
        String sSQL;
        String sWhere;
        String aux;
        String additional = "";

    /* get name of fields*/
        sSQL = "";
        for (int i = 0; i < _fieldName.size(); i++)
        {
            if (!_fieldName.get(i).equals(""))
            {
                if (sSQL.equals(""))
                        sSQL = _fieldName.get(i);
                else
                        sSQL = sSQL + ", " + _fieldName.get(i);
            }
        }

         /* get where*/
        if (_conditionalWhere.size() != 0)
        {
                sWhere = " WHERE " + _conditionalWhere.get(0);
                for (int i = 1; i < _conditionalWhere.size(); i++)
                {
                        if (_junction.size() >= i)
                                aux = _junction.get(i-1);
                        else
                                aux = "AND";
                        sWhere += " "+aux+" "+_conditionalWhere.get(i);
                }
        }
        else
                sWhere = "";

        //additional
        for (int i=0; i < _additional.size(); i++)
        {
                additional+= " "+_additional.get(i);
        }
        return "SELECT " +sSQL + " FROM "+ _table + sWhere + " " +additional;
    }


    public void prepare()
    {
        //fTable = "";
        _fieldName.clear();
        _fieldValue.clear();
        _conditionalWhere.clear();
        _additional.clear();
        _sql = "";
    }


    public String removeFormat(String Text)
    {
        String sNotFormat = "";
        char sItem;
        for (int i = 1; i < Text.length(); i++)
        {
            sItem = Text.charAt(i);

            if ((sItem >= '0' && sItem <= '9') || (sItem >= 'A' && sItem <= 'Z') || (sItem >= 'a' && sItem <= 'z'))
                sNotFormat = sNotFormat + sItem;
        }
        return sNotFormat;
    }


    public void setFieldsName(ArrayList<String> AValue)
    {
        _fieldName = AValue;
    }


    public void setFieldsValue(ArrayList<String> AValue)
    {
        _fieldValue = AValue;
    }


    public void setValue(double AValue, String AFieldName, String AFormat)
    {
        _fieldName.add(AFieldName);
        try
        {
            _fieldValue.add(String.valueOf(AValue).replace(',', '.'));
        }
        catch(Exception e)
        {
            _fieldValue.add("0.00");
        }
        //fFieldsValue.Add(FormataValue(AValue, AFormat, ASeparatorDecimal, ASeparatorMilhar));
    }


    public void setValue(double AValue, String AFieldName)
    {
        _fieldName.add(AFieldName);
        try
        {
            String temp = String.valueOf(AValue).replace(',', '.');
            _fieldValue.add(temp);
        }
        catch(Exception e)
        {
            _fieldValue.add("0.00");
        }
        //fFieldsValue.Add(FormataValue(AValue, AFormat, ASeparatorDecimal, ASeparatorMilhar));
    }


    public void setValue(Boolean AValue, String AFieldName)
    {
        try
        {
            if ((AValue == null))
                return;
            _fieldName.add(AFieldName);
            _fieldValue.add(AValue.toString());
        }
        catch(Exception e)
        {
            return;
        }
    }
    
    
    public void setValue(String AValue, String AFieldName)
    {
        try
        {
            if ( ((AValue == null) || (AValue.equals(""))) && (!AFieldName.equals("*")))
                return;
            _fieldName.add(AFieldName);
            _fieldValue.add(AValue);
        }
        catch(Exception e)
        {
            return;
        }
    }

    public void setAsDateTime(String value, String field)
    {

            try
            {
                    setValue(Functions.formatDateInternational(value, Constants.DATE_FORMAT_BRAZILIAN ), field);
            }
            catch(Exception e)
            {
                    setValue("", field);
            }
    }
}

